import mysql.connector
from mysql.connector import pooling, errorcode
from com.linkstec.config.database import DatabaseConfig
import logging

logger = logging.getLogger(__name__)


class DatabasePool:
    _instance = None

    @classmethod
    def get_instance(cls):
        if cls._instance is None:
            try:
                cls._instance = pooling.MySQLConnectionPool(
                    pool_size=DatabaseConfig.POOL_SIZE,
                    host=DatabaseConfig.DB_HOST,
                    port=DatabaseConfig.DB_PORT,
                    user=DatabaseConfig.DB_USER,
                    password=DatabaseConfig.DB_PASSWORD,
                    database=DatabaseConfig.DB_NAME
                )
                logger.info("Database connection pool created successfully")
            except mysql.connector.Error as err:
                if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
                    logger.error("Database access denied")
                elif err.errno == errorcode.ER_BAD_DB_ERROR:
                    logger.error("Database does not exist")
                else:
                    logger.error(f"Database connection error: {err}")
                raise
        return cls._instance


class TransactionContext:
    def __init__(self):
        self.conn = None

    def __enter__(self):
        self.conn = DatabasePool.get_instance().get_connection()
        self.conn.start_transaction()
        return self.conn

    def __exit__(self, exc_type, exc_val, exc_tb):
        if exc_type:
            self.conn.rollback()
            logger.error("Transaction rolled back due to error")
        else:
            self.conn.commit()
            logger.info("Transaction committed successfully")
        self.conn.close()